# -*- coding: utf-8 -*-
import hashlib
import jwt
from flask import Blueprint, jsonify
from flask_restful import Api, request, Resource, reqparse
from openpyxl import load_workbook
import xlrd
import requests
from flask import g
from models.model import UserModel, db, ClassModel, VipModel
from selenium.webdriver.common.by import By
import ddddocr
import threading
import os
import concurrent.futures
import time
import pymysql
from utils.spiders1_get_class_name import get_excel
from selenium import webdriver
from selenium.webdriver.chrome.service import Service
all_bp = Blueprint("all_bp", __name__)
api = Api(all_bp)


# 1. 次数改为查询账号次数

def aa(func):
    def bb(*args, **kwargs):
        token = request.headers.get('token')
        if not token:
            return jsonify({
                'code': 400,
                'msg': "token不存在"
            })
        try:
            payload = jwt.decode(token, '12345678910111213', algorithms='HS256')
        except Exception as e:
            print(e)
            return jsonify({
                'code': 200,
                'msg': "解码失败"
            })
        a = VipModel.query.filter(VipModel.id == payload.get('id')).first()
        g.user_id = payload.get('id')
        g.vip_level = int(a.vip_level)

        return func(*args, **kwargs)

    return bb


def get_class(data):
    chrome_options = webdriver.ChromeOptions()
    chrome_options.add_argument("disable-cache")
    chrome_options.add_experimental_option('excludeSwitches', ['enable-logging'])
    service_dir = os.getcwd().replace('\\','/').replace('views','chromedriver_win32/chromedriver.exe')

    wd = webdriver.Chrome(options=chrome_options,service=Service(service_dir))
    wd.implicitly_wait(3)
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='123456',
        db='spiders',
        charset='utf8mb4'
    )

    cursor = conn.cursor()
    user_names = data['username']
    passwords = data['password']
    wd.get('https://iam.pt.ouchn.cn/am/UI/Login')
    user_name = wd.find_element(By.ID, 'loginName')
    password = wd.find_element(By.ID, 'password')
    login = wd.find_element(By.ID, 'button')
    img_code = wd.find_element(By.ID, 'validateCode')
    img = wd.find_element(By.ID, 'kaptchaImage')
    current_dir = os.getcwd()
    current_dir = current_dir.replace('\\', '/')
    img_name = str(current_dir + f"/{str(user_names)}.png")

    with open(img_name, 'wb') as f:
        f.write(img.screenshot_as_png)

    ocr = ddddocr.DdddOcr()
    with open(img_name, 'rb') as f:
        img_bytes = f.read()
    code = ocr.classification(img_bytes)
    if len(code) == 5:
        code = code[1:]

    user_name.send_keys(user_names)
    password.send_keys(passwords)

    img_code.send_keys(code)

    if os.path.exists(img_name):
        os.remove(img_name)
    else:
        pass
    login.click()
    try:
        wd.find_element(By.CSS_SELECTOR, 'body > div > div.box > img')
        sql = "INSERT INTO class_model (stu_num, username, class_name,password) VALUES (%s, %s, %s,%s)"
        val = (user_names, passwords, '出现错误', '账号或密码失效')
        cursor.execute(sql, val)
        conn.commit()
        cursor.close()
        conn.close()
        return
    except:
        pass

    try:
        wd.find_element(By.CSS_SELECTOR, 'body > div > div.box.box-spaced.clear-float > div.grid_3 > div')
        sql = "INSERT INTO class_model (stu_num, username, class_name,password) VALUES (%s, %s, %s,%s)"
        val = (user_names, passwords, '出现错误', '账号已被锁定，一般是爬取次数过多的问题')
        cursor.execute(sql, val)
        conn.commit()
        cursor.close()
        conn.close()
    except:
        pass

    wd.get('https://menhu.pt.ouchn.cn/site/ouchnPc/index')
    time.sleep(2)
    name = ""
    try:
        name = wd.find_element(By.CSS_SELECTOR,
                               'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_info.w > div.swiper-wrap > div.swiper-container.swiper-container-horizontal > div.swiper-wrapper > div.swiper-slide.ouchnPc_index_info_left_teacher.swiper-slide-active > div > div.photo_name.student_height > div > span')
        name = name.get_attribute('innerHTML')
    except Exception as e:
        wd.get('https://menhu.pt.ouchn.cn/site/ouchnPc/index')
        name = wd.find_element(By.CSS_SELECTOR,
                               'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_info.w > div.swiper-wrap > div.swiper-container.swiper-container-horizontal > div.swiper-wrapper > div.swiper-slide.ouchnPc_index_info_left_teacher.swiper-slide-active > div > div.photo_name.student_height > div > span')
        name = name.get_attribute('innerHTML')

    now_total_element = wd.find_element(By.CSS_SELECTOR,
                                        "body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > div > span.el-pagination__total")
    now_total = now_total_element.get_attribute("innerHTML").split(" ")[1]
    if now_total != 0:
        if int(now_total) <= 6:
            for i in range(1, int(now_total) + 1):
                class_name = wd.find_element(By.CSS_SELECTOR,
                                             'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % (
                                                 i))

                sql = "INSERT INTO class_model (stu_num, username, class_name,password) VALUES (%s, %s, %s,%s)"
                # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                val = (name, user_names, class_name.get_attribute('innerHTML').strip(), passwords)
                cursor.execute(sql, val)

                conn.commit()
        else:
            page_count = int(now_total) // 6
            last_count = int(now_total) - page_count * 6
            for j in range(page_count):
                for i in range(1, 7):
                    class_name = wd.find_element(By.CSS_SELECTOR,
                                                 'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % i)

                    sql = "INSERT INTO class_model (stu_num, username, class_name,password) VALUES (%s, %s, %s,%s)"
                    # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                    val = (
                        name, user_names, class_name.get_attribute('innerHTML').strip(),
                        passwords)
                    cursor.execute(sql, val)

                    conn.commit()
                aaa = wd.find_element(By.CSS_SELECTOR,
                                      'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > div > button.btn-next')
                wd.execute_script("arguments[0].click();", aaa)
                time.sleep(1)
            for i in range(1, last_count + 1):
                class_name = wd.find_element(By.CSS_SELECTOR,
                                             'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % i)

                sql = "INSERT INTO class_model (stu_num, username, class_name,password) VALUES (%s, %s, %s,%s)"
                # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                val = (name, user_names, class_name.get_attribute('innerHTML').strip(), passwords)
                cursor.execute(sql, val)

                conn.commit()

    element = wd.find_element(By.CSS_SELECTOR,
                              'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > section > span:nth-child(2)')
    wd.execute_script("arguments[0].click();", element)

    time.sleep(1)
    now_total_element = wd.find_element(By.CSS_SELECTOR,
                                        "body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > div > span.el-pagination__total")  # 总共有几条数据
    now_total = now_total_element.get_attribute("innerHTML").split(" ")[1]
    if now_total != 0:
        if int(now_total) <= 6:
            for i in range(1, int(now_total) + 1):
                class_name = wd.find_element(By.CSS_SELECTOR,
                                             'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % (
                                                 i))

                sql = "INSERT INTO class_model (stu_num, username, class_name,password) VALUES (%s, %s, %s,%s)"

                # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                val = (name, user_names, class_name.get_attribute('innerHTML').strip(), passwords)
                cursor.execute(sql, val)

                conn.commit()
        else:
            page_count = int(now_total) // 6
            last_count = int(now_total) - page_count * 6
            for j in range(page_count):
                for i in range(1, 7):
                    class_name = wd.find_element(By.CSS_SELECTOR,
                                                 'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % i)

                    sql = "INSERT INTO class_model (stu_num, username, class_name,password) VALUES (%s, %s, %s,%s)"
                    # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                    val = (
                        name, user_names, class_name.get_attribute('innerHTML').strip(), passwords)
                    cursor.execute(sql, val)
                    conn.commit()
                bbb = wd.find_element(By.CSS_SELECTOR,
                                      'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > div > button.btn-next')
                wd.execute_script("arguments[0].click();", bbb)
                time.sleep(1)
            for i in range(1, last_count + 1):
                class_name = wd.find_element(By.CSS_SELECTOR,
                                             'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % i)
                sql = "INSERT INTO class_model (stu_num, username, class_name,password) VALUES (%s, %s, %s,%s)"
                # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                val = (name, user_names, class_name.get_attribute('innerHTML').strip(), passwords)
                cursor.execute(sql, val)
                conn.commit()

    cursor.close()
    conn.close()
    return


def get_class2(lis):
    chunk_size = 3
    with concurrent.futures.ThreadPoolExecutor() as executor:
        # 提交线程任务
        while True:
            data_chunk = lis[:chunk_size]  # 取出3个数据
            if not data_chunk:
                break
            futures = [executor.submit(get_class, n) for n in data_chunk]
            # 等待所有线程完成
            results = [future.result() for future in concurrent.futures.as_completed(futures)]
            lis = lis[chunk_size:]


def process_thread(data_list):
    while True:
        try:
            data = data_list.pop(0)
        except IndexError:
            break
        else:

            g.result_queue.put(get_class(data))


def threading_start(data):
    thread_list = []
    for i in range(3):
        t = threading.Thread(target=process_thread, args=[data])
        thread_list.append(t)
        t.start()
    for t in thread_list:
        t.join()
    result_list = []
    while not g.result_queue.empty():
        result_list.append(g.result_queue.get())
    return result_list


def md5_jia(a):
    a = str(a) + 'awdadbnaiwudbaiudnhaiwudai'
    md5_hash = hashlib.md5(a.encode('utf-8')).hexdigest()
    return md5_hash


class Ceshi(Resource):
    @aa
    def post(self):
        uid = g.user_id
        a = VipModel.query.filter(VipModel.id == uid).first()
        if int(a.vip_level) != 1:
            if int(a.num) == 0:
                return jsonify({
                    'code': 200,
                    'msg': "次数为0，不可操作"
                })

        file = request.files['file']
        name = request.form.get('name')
        filename = file.filename
        lis = []
        if filename.endswith('.xls'):
            workbook = xlrd.open_workbook(file_contents=file.read())
            worksheet = workbook.sheet_by_index(0)
            lis = []
            for row in range(1, worksheet.nrows):
                row_data = []
                for col in range(worksheet.ncols):
                    cell_value = worksheet.cell_value(row, col)
                    row_data.append(cell_value)
                row_data[0] = str(row_data[0]).split('.')[0]
                row_data = {
                    'username': row_data[0],
                    'password': row_data[1]
                }
                lis.append(row_data)
        elif filename.endswith('.xlsx'):
            wb = load_workbook(file, read_only=True)
            ws = wb.active
            rows = ws.iter_rows()
            data = []
            # 去重列表
            # 遍历数据行，将数据保存到列表中
            for row in rows:
                row_data = []
                for cell in row:
                    row_data.append(cell.value)
                data.append(row_data)
            for i in data[1:]:
                if i[0] == None:
                    continue
                if str(i[0]).isalnum() == True:
                    a = {'username': i[0], 'password': i[1]}
                else:
                    a = {'username': "错误账号", 'password': f"{str(i[0]).replace('.', '').replace('e+17', '')}_{i[1]}"}
                if a in lis:
                    pass
                else:
                    lis.append(a)
        if a.vip_level != 1:
            if len(lis) >= int(a.num):
                lis = lis[:int(a.num)]
            if int(a.vip_level) == 2:
                a.num = int(a.num) - len(lis)
            db.session.commit()
        for i in lis:
            b = UserModel(username=str(i['username']).replace('.', ''), password=str(i['password']).replace(' ', ''))
            db.session.add(b)
            db.session.commit()
        return jsonify({
            'code': 200,
            'msg': "上传成功",
        })


class Ceshi1(Resource):
    @aa
    def get(self):
        uid = g.user_id
        a = VipModel.query.filter(VipModel.id == uid).first()
        if int(a.vip_level) != 1:
            if int(a.num) == 0:
                return jsonify({
                    'code': 200,
                    'msg': "次数为0，不可操作"
                })
        # conn = pymysql.connect(
        #     host='localhost',
        #     port=3306,
        #     user='root',
        #     password='123456',
        #     db='spiders',
        #     charset='utf8mb4'
        # )
        # # 获取游标
        # cursor = conn.cursor()
        # sql = "SELECT * FROM user_model"
        # cursor.execute(sql)
        # rows = cursor.fetchall()
        data_list = UserModel.query.filter(UserModel.username != '错误账号').all()
        lis = []
        for i in data_list:
            lis.append({
                'username': i.username,
                'password': i.password
            })
        chunk_size = 3
        with concurrent.futures.ThreadPoolExecutor() as executor:
            # 提交线程任务
            while True:
                data_chunk = lis[:chunk_size]  # 取出3个数据
                if not data_chunk:
                    break
                futures = [executor.submit(get_class, n) for n in data_chunk]
                # 等待所有线程完成
                results = [future.result() for future in concurrent.futures.as_completed(futures)]
                lis = lis[chunk_size:]

        # lis1 = []
        # worring_lis = ClassModel.query.filter(ClassModel.class_name == '出现错误').all()
        # for i in worring_lis:
        #     lis1.append({
        #         'username': i.stu_num,
        #         'password': i.username
        #     })
        # get_class2(lis1)

        return jsonify({
            'code': 200,
            'msg': "爬取成功",
        })


class Ceshi2(Resource):
    @aa
    def get(self):
        uid = g.user_id
        a = VipModel.query.filter(VipModel.id == uid).first()
        if int(a.vip_level) != 1:
            if int(a.num) == 0:
                return jsonify({
                    'code': 200,
                    'msg': "次数为0，不可操作"
                })
        name = '工作簿'
        class_lis = ClassModel.query.filter(ClassModel.class_name != '出现错误').order_by(ClassModel.username.asc()).all()
        lis = []
        for i in class_lis:
            lis.append({
                'stu_num': i.stu_num,
                'username': i.username,
                'class_name': i.class_name
            })
        get_excel(lis, name)

        worring_lis1 = []
        user_lis = UserModel.query.filter(UserModel.username == '错误账号')

        for i in user_lis:
            worring_lis1.append({
                'username': str(i.password).split('_')[0],
                'password': str(i.password).split('_')[1],
                'cause': "账号长度过长，读取出现问题，该账号为原账号前十五位"
            })
        user_lis2 = ClassModel.query.filter(ClassModel.class_name == '出现错误').all()

        for i in user_lis2:
            worring_lis1.append({
                'username': i.stu_num,
                'password': i.username,
                'cause': i.password
            })
        # db.session.query(ClassModel).delete()  # 删除表中所有数据
        # db.session.commit()
        # db.session.query(UserModel).delete()  # 删除表中所有数据
        # db.session.commit()
        return jsonify({
            'code': 200,
            'msg': "下载成功",
            'data': worring_lis1
        })


class Ceshi3(Resource):
    def get(self):
        req = reqparse.RequestParser()
        req.add_argument('username')
        req.add_argument('password')
        args = req.parse_args()
        a = VipModel.query.filter(VipModel.username == args['username']).first()

        if not a:
            return jsonify({
                'code': 500,
                'msg': '账号不存在'
            })
        else:
            if md5_jia(args['password']) != a.password:
                return jsonify({
                    'code': 400,
                    'msg': "密码错误"
                })
        if a.status == '冻结':
            return jsonify({
                'code': 400,
                'msg': '该账号已被冻结'
            })

        a.num = int(a.num) - 1
        token = self.get_token(a)
        return jsonify({
            'code': 200,
            'msg': "登录成功",
            'data': token
        })

    @aa
    def post(self):
        user_vip = g.vip_level
        if user_vip != 1:
            return jsonify({
                'code': 400,
                'msg': "没有权限"
            })
        req = reqparse.RequestParser()
        req.add_argument('username')
        req.add_argument('password')
        req.add_argument('num')
        args = req.parse_args()
        if args['num'] == "":
            args['num'] = 3
        a = VipModel.query.filter(VipModel.username == args['username']).first()
        if a:
            return jsonify({
                'code': 500,
                'msg': '账号已存在'
            })
        else:
            new_student = VipModel(username=args['username'], password=md5_jia(args['password']), vip_level=int(2),
                                   num=int(args['num']), status='激活')
            db.session.add(new_student)
            db.session.commit()
            return jsonify({
                'code': 200,
                'msg': "添加成功"
            })


class Ceshi4(Resource):
    @aa
    def put(self):
        user_vip = g.vip_level
        if user_vip != 1:
            return jsonify({
                'code': 400,
                'msg': "没有权限"
            })
        req = reqparse.RequestParser()
        req.add_argument('id')
        req.add_argument('status')
        req.add_argument('password')
        req.add_argument('num')
        args = req.parse_args()
        a = VipModel.query.filter(VipModel.id == args['id']).first()
        if int(a.vip_level) != 1:
            a.status = args['status']
            a.num = int(args['num'])
        a.password = md5_jia(args['password'])
        db.session.commit()
        return jsonify({
            'code': 200,
            'msg': "已修改"
        })

    @aa
    def delete(self):
        user_vip = g.vip_level
        if user_vip != 1:
            return jsonify({
                'code': 400,
                'msg': "没有权限"
            })

        req = reqparse.RequestParser()
        req.add_argument('id')
        args = req.parse_args()

        a = VipModel.query.filter(VipModel.id == args['id']).first()
        if int(a.vip_level) == 1:
            return jsonify({
                'code': 400,
                'msg': "主管理不可删除"
            })
        student = VipModel.query.get(args['id'])
        # 删除指定的学生信息
        db.session.delete(student)
        db.session.commit()
        return jsonify({
            'code': 200,
            'msg': "已删除"
        })


class Ceshi6(Resource):
    @aa
    def put(self):
        user_vip = g.vip_level
        if user_vip != 1:
            return jsonify({
                'code': 400,
                'msg': "没有权限"
            })
        req = reqparse.RequestParser()
        req.add_argument('id')
        req.add_argument('status')
        args = req.parse_args()
        a = VipModel.query.filter(VipModel.id == args['id']).first()
        a.status = args['status']
        db.session.commit()
        return jsonify({
            'code': 200,
            'msg': "已修改"
        })


class Ceshi5(Resource):
    def get(self):
        a = VipModel.query.all()
        lis = []
        for i in a:
            lis.append({
                'id': i.id,
                'username': i.username,
                'vip_level': i.vip_level,
                'status': i.status,
                'num': i.num
            })
        return jsonify({
            'code': 200,
            'msg': "获取数据成功",
            'data': lis
        })

    def post(self):
        req = reqparse.RequestParser()
        req.add_argument('username')
        req.add_argument('password')
        args = req.parse_args()
        a = VipModel.query.filter(VipModel.username == args['username']).first()
        if not a:
            return jsonify({
                'code': 500,
                'msg': '账号不存在'
            })
        else:
            if md5_jia(args['password']) != a.password:
                return jsonify({
                    'code': 400,
                    'msg': "密码错误"
                })
        if a.status == '冻结':
            return jsonify({
                'code': 400,
                'msg': '该账号已被冻结'
            })

        if int(a.vip_level) == 2:
            a.num = int(a.num) - 1
        db.session.commit()
        token = self.get_token(a)
        return jsonify({
            'code': 200,
            'msg': "登录成功",
            'token': token
        })

    @staticmethod
    def get_token(user):
        payload = {
            'id': user.id,
            'exp': int(time.time()) + 3600 * 3
        }
        token = jwt.encode(payload, '12345678910111213')
        return token


class Ceshi7(Resource):
    def get(self):
        db.session.query(ClassModel).delete()
        db.session.query(UserModel).delete()
        current_path = os.path.abspath(os.path.dirname(__file__))
        parent_path = os.path.abspath(os.path.join(current_path, os.path.pardir))
        parent_path = parent_path.replace('\\', '/')
        for filename in os.listdir(parent_path):
            if filename.endswith('.jpg') or filename.endswith('.png'):
                file_path = os.path.join(parent_path, filename)
                os.remove(file_path)
        db.session.commit()
        return jsonify({
            'code': 200,
            'msg': "删除成功"
        })


class Ceshi8(Resource):
    @aa
    def get(self):
        uid = g.user_id
        a = VipModel.query.filter(VipModel.id == uid).first()
        if int(a.vip_level) != 1:

            if int(a.num) == 0:
                return jsonify({
                    'code': 200,
                    'msg': "次数为0，不可操作"
                })
        req = reqparse.RequestParser()
        req.add_argument('username')
        req.add_argument('password')
        args = req.parse_args()
        data = {
            'username': args['username'],
            'password': args['password']
        }
        if int(a.vip_level) != 1:
            if int(a.vip_level) == 2 and int(a.num) >= 1:
                a.num = int(a.num) - 1
            db.session.commit()
        get_class(data)
        a = ClassModel.query.all()
        aa = ''
        for i in a:
            aa += (i.class_name + '|')

        lis = []
        for i in a:
            lis.append({
                'stu_num': i.stu_num,
                'username': i.username,
                'password': i.password,
                'class_name': aa
            })
            break

        db.session.query(ClassModel).delete()
        db.session.commit()

        return jsonify({
            'code': 200,
            'msg': "爬取数据成功",
            'data': lis
        })


api.add_resource(Ceshi, '/a')
api.add_resource(Ceshi1, '/b')
api.add_resource(Ceshi2, '/c')
api.add_resource(Ceshi3, '/d')
api.add_resource(Ceshi4, '/e')
api.add_resource(Ceshi5, '/f')
api.add_resource(Ceshi6, '/g')
api.add_resource(Ceshi7, '/h')
api.add_resource(Ceshi8, '/i')
